package cn.mesmile.clickhouse.demo;

import cn.mesmile.clickhouse.entity.Book;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

/**
 * @author zb
 * @date 2021/6/29 22:49
 * @Description
 */
public class ClickHouseDemo {

    /**
     *  默认配置下，如果我们连接远程服务器上的 clickhouse，会出现 Connection refuse 异常是 clickhouse 本身的配置问题。
     *  在 clickhouse 的配置文件 /etc/clickhouse-server/config.xml
     *  如果我们想监听来自任意主机的请求，可以增加如下配置： <listen_host>::</listen_host>
     */

    static Statement statement = null;
    static DataSource dataSource = null;
    static {
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
//            Connection connection = DriverManager.getConnection("jdbc:clickhouse://81.69.43.78:8123");
//            statement = connection.createStatement();
            ClickHouseProperties properties = new ClickHouseProperties();
            properties.setSocketTimeout(300000);
            dataSource = new ClickHouseDataSource("jdbc:clickhouse://81.69.43.78:8123", properties);
            Connection connection = dataSource.getConnection();
            statement = connection.createStatement();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // 创建数据库 create database if not exists db_name [engine = db_engine]
        // 创建表
//        createTable();
        // 添加数据
//        batchInsert();
        // 查询数据
        queryData2();
        // 删除表
//        deleteTable();
        //
//        queryDataMore();
    }

    private static void queryData2() {
        try {
            String sql = "select * from tutorial.book where id = ?";
            QueryRunner queryRunner = new QueryRunner(dataSource);
            // 最后一个可变参数
            // 返回[单个] new BeanHandler
            List<Book> query = queryRunner.query(sql, new BeanListHandler<Book>(Book.class),0);
            System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
            query.forEach(System.out::println);
            //
//            DbUtils.close(statement);
//            DbUtils.close(dataSource.getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteTable() {
        try {
            statement.executeQuery("drop table tutorial.jdbc_example");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void queryDataMore() {
        try {
            String sql = "select * from tutorial.visits_v1 limit 10";
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                // ResultSet 的下标值从 1 开始，不可使用 0，否则越界，报 ArrayIndexOutOfBoundsException 异常
                System.out.println("rs = " + rs);
//                System.out.println(rs.getDate(1) + ", " + rs.getString(2) + ", " + rs.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void queryData() {
        try {
            String sql = "select * from tutorial.jdbc_example";
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                // ResultSet 的下标值从 1 开始，不可使用 0，否则越界，报 ArrayIndexOutOfBoundsException 异常
                System.out.println(rs.getDate(1) + ", " + rs.getString(2) + ", " + rs.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void batchInsert() {
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            Connection connection = DriverManager.getConnection("jdbc:clickhouse://81.69.43.78:8123");
            PreparedStatement pstmt = connection.prepareStatement("insert into tutorial.jdbc_example values(?, ?, ?)");
            // insert 10 records
            for (int i = 0; i < 10; i++) {
                pstmt.setDate(1, new Date(System.currentTimeMillis()));
                pstmt.setString(2, "panda_" + (i + 1));
                pstmt.setInt(3, 18+i);
                pstmt.addBatch();
            }
            int[] ints = pstmt.executeBatch();
            System.out.println("-------------------------------------------------------");
            System.out.println("ints = " + ints.length);
            System.out.println("-------------------------------------------------------");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void createTable() {
        try {
            ResultSet resultSet = statement.executeQuery("create table if not exists tutorial.jdbc_example(day Date, name String, age UInt8) Engine=Log");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
